using System.Data;
using Microsoft.Extensions.Options;
using SPI.AgentServer.Configuration;
using SPI.AgentServer.Services.Execution;
using SPI.AgentServer.Services.Security;

namespace SPI.AgentServer.Services.Tools;

/// <summary>
/// SQL 执行工具 - 用于执行简单的 SQL 查询
/// 特点：
/// 1. 仅支持 SELECT 查询
/// 2. 自动添加结果限制（LIMIT/TOP）
/// 3. 包含 SQL 安全验证
/// </summary>
public class ExecuteSqlTool : IQueryTool
{
    private readonly IDatabaseExecutor _executor;
    private readonly ISqlValidator _validator;
    private readonly DatabaseConfiguration _config;
    private readonly ILogger<ExecuteSqlTool> _logger;

    public string Name => "execute_sql";
    public string Description => "Execute SQL query (SELECT only)";

    public ExecuteSqlTool(
        IDatabaseExecutor executor,
        ISqlValidator validator,
        IOptions<DatabaseConfiguration> options,
        ILogger<ExecuteSqlTool> logger)
    {
        _executor = executor;
        _validator = validator;
        _config = options.Value;
        _logger = logger;
    }

    /// <summary>
    /// 执行 SQL 查询
    /// </summary>
    /// <param name="parameters">参数字典，必须包含 "sql" 参数，可选 "database" 参数</param>
    /// <param name="cancellationToken">取消令牌</param>
    /// <returns>执行结果</returns>
    public async Task<ToolResult> ExecuteAsync(Dictionary<string, object> parameters, CancellationToken cancellationToken = default)
    {
        // 验证参数是否完整
        if (!ValidateParameters(parameters, out var errors))
        {
            return new ToolResult
            {
                Success = false,
                Errors = errors
            };
        }

        var sql = parameters["sql"].ToString()!;
        // 兼容 database 和 databaseName 两种参数名
        var database = parameters.ContainsKey("database") ? parameters["database"]?.ToString() 
                     : parameters.ContainsKey("databaseName") ? parameters["databaseName"]?.ToString() 
                     : null;

        try
        {
            // 步骤 1：验证 SQL 安全性
            // 检查是否包含危险操作、SQL 注入等
            var validationResult = await _validator.ValidateAsync(sql);
            if (!validationResult.IsValid)
            {
                return new ToolResult
                {
                    Success = false,
                    Errors = validationResult.Errors
                };
            }

            // 步骤 2：自动添加结果限制
            // 避免查询返回过多数据影响性能
            if (!sql.Contains("TOP", StringComparison.OrdinalIgnoreCase) && 
                !sql.Contains("LIMIT", StringComparison.OrdinalIgnoreCase))
            {
                sql = AddLimit(sql, _config.MaxResults);
            }

            // Execute query
            var result = await _executor.ExecuteQueryAsync(sql, database, cancellationToken);

            return new ToolResult
            {
                Success = true,
                Data = result.Data,
                Metadata = new Dictionary<string, object>
                {
                    ["rowCount"] = result.RowCount,
                    ["executionTimeMs"] = result.ExecutionTimeMs,
                    ["database"] = database ?? "default"
                }
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error executing SQL: {Sql}", sql);
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { $"Execution error: {ex.Message}" }
            };
        }
    }

    public bool ValidateParameters(Dictionary<string, object> parameters, out List<string> errors)
    {
        errors = new List<string>();

        if (!parameters.ContainsKey("sql") || string.IsNullOrWhiteSpace(parameters["sql"]?.ToString()))
        {
            errors.Add("Parameter 'sql' is required and cannot be empty");
        }

        return errors.Count == 0;
    }

    private string AddLimit(string sql, int limit)
    {
        sql = sql.TrimEnd(';');

        // Skip LIMIT for aggregate queries (COUNT, SUM, AVG, MIN, MAX)
        if (sql.Contains("COUNT(", StringComparison.OrdinalIgnoreCase) ||
            sql.Contains("SUM(", StringComparison.OrdinalIgnoreCase) ||
            sql.Contains("AVG(", StringComparison.OrdinalIgnoreCase) ||
            sql.Contains("MIN(", StringComparison.OrdinalIgnoreCase) ||
            sql.Contains("MAX(", StringComparison.OrdinalIgnoreCase))
        {
            return sql;
        }

        // MySQL/PostgreSQL use LIMIT at end
        return $"{sql} LIMIT {limit}";
    }
}
